﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
//using Oracle.DataAccess.Client;
using System.Configuration;
//using System.Data.Entity.Infrastructure;
using System.ComponentModel.DataAnnotations;
using DslamPort.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;
using System.Data;
using System.Globalization;

namespace DslamPort.Models
{
    public class DslamPortModel
    {
        public string id { get; set; }
        [Required(ErrorMessage = "System ID không được để trống!")]
        public string system_id { get; set; }
        public string name { get; set; }
        public string rackid { get; set; }
        public string selfid { get; set; }
        public string slot { get; set; }
        public string port { get; set; }
        public string vpi { get; set; }
        public string vci { get; set; }
        public string account_int { get; set; }
        public string goidv_int { get; set; }
        public string account_mytv { get; set; }
        public string goidv_mytv { get; set; }

        public string ma_kh { get; set; }
        //[Required(ErrorMessage = "Tên khách hàng không được để trống!")]
        public string ten_kh { get; set; }
        public string trang_thai_int { get; set; }
        public string trang_thai_mytv { get; set; }
        public string ip_tinh { get; set; }
        public string hinh_thuc_int { get; set; }
        //[Required(ErrorMessage = "Địa chỉ khách hàng không được để trống!")]
        public string diachi_kh { get; set; }
        public string ngay_dk_int { get; set; }
        public string bras_ip { get; set; }
        public string user_name { get; set; }
        public string status { get; set; }
        public string cong { get; set; }
        public string ip { get; set; }
        public string dung_luong { get; set; }
        public string vlan { get; set; }
        public string dslam_type_id { get; set; }
        public string xa_phuong_id { get; set; }
        public string quan_huyen_id { get; set; }
        public string todanpho_id { get; set; }
        public static List<DslamPortModel> getDslamPort(string str = "", string id = "",string timkiem_type="")
        {
            List<DslamPortModel> DslamPort = new List<DslamPortModel>();
            string sql = "";
            bool status = false;
            if (str == "")
                status = true;
            else
                if (!str.Contains("a.system_id"))
                    status = true;
            if (timkiem_type != "")
                status = false;
            if (status)
                sql = "SELECT a.*,c.quan_huyen_id ,b.xa_phuong_id,b.todanpho_id,b.name,b.ip,b.vlan,b.dslam_type_id,c.ten tendonvi  FROM dslam_port a,dslam b, quan_huyen c where c.quan_huyen_id=-1";
            else
                sql = "SELECT a.*,c.quan_huyen_id ,b.xa_phuong_id,b.todanpho_id,b.name,b.ip,b.vlan,b.dslam_type_id,c.ten tendonvi  FROM dslam_port a,dslam b, quan_huyen c where a.system_id=b.system_id and b.quan_huyen_id=c.quan_huyen_id " + str + " order by a.system_id,to_number(rackid),to_number(selfid),to_number(slot),to_number(port),to_number(vpi),to_number(vci)";
            if (id != "")
                sql = "SELECT a.*,c.quan_huyen_id ,b.xa_phuong_id,b.todanpho_id,b.name,b.ip,b.vlan,b.dslam_type_id,c.ten tendonvi  FROM dslam_port a,dslam b, quan_huyen c where a.system_id=b.system_id and b.quan_huyen_id=c.quan_huyen_id and a.id=" + id + " order by a.system_id,to_number(rackid),to_number(selfid),to_number(slot),to_number(port),to_number(vpi),to_number(vci)";
            var odr = BaseClass.Lib_GetDataReader(sql);
            //int ngay_column_index = 0;
            foreach (DataRow item in odr.Rows)
            {
                DslamPort.Add(new DslamPortModel
                {
                    id = item["id"].ToString(),
                    system_id = item["system_id"].ToString(),
                    name = item["name"].ToString(),
                    rackid = item["rackid"].ToString(),
                    selfid = item["selfid"].ToString(),
                    slot = item["slot"].ToString(),
                    port = item["port"].ToString(),
                    vpi = item["vpi"].ToString(),
                    vci = item["vci"].ToString(),
                    account_int = item["account_int"].ToString(),
                    goidv_int = item["goidv_int"].ToString(),
                    account_mytv = item["account_mytv"].ToString(),
                    goidv_mytv = item["goidv_mytv"].ToString(),
                    ma_kh = item["ma_kh"].ToString(),
                    ten_kh = item["ten_kh"].ToString(),
                    trang_thai_int = item["trang_thai_int"].ToString(),
                    trang_thai_mytv = item["trang_thai_mytv"].ToString(),
                    ip_tinh = item["ip_tinh"].ToString(),
                    hinh_thuc_int = item["hinh_thuc_int"].ToString(),
                    diachi_kh = item["diachi_kh"].ToString(),
                    //ngay_dk_int = odr.IsDBNull(ngay_column_index) ? null : odr.GetDateTime(ngay_column_index).ToString("dd'/'MM'/'yyyy HH:mm:ss"),
                    ngay_dk_int =  item["ngay_dk_int"].ToString() == "" ? null:((DateTime)item["ngay_dk_int"]).ToString("dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture),
                    bras_ip = item["bras_ip"].ToString(),
                    user_name = item["user_name"].ToString(),
                    status = item["status"].ToString(),
                    cong = item["cong"].ToString(),
                    dslam_type_id = item["dslam_type_id"].ToString(),
                    dung_luong = item["dung_luong"].ToString(),
                    vlan = item["vlan"].ToString(),
                    ip = item["ip"].ToString(),
                    quan_huyen_id = item["quan_huyen_id"].ToString(),
                    todanpho_id = item["todanpho_id"].ToString(),
                    xa_phuong_id = item["xa_phuong_id"].ToString()
                });

            }
            
            return DslamPort;
        }

        public void DeleteDslamPort(string id)
        {
            string sql = "delete from dslam_port where id=" + id;
            BaseClass.Lib_ExecuteNonQuery(sql);
        }
        public void updateDslam(DslamPortModel DslamPort, string type = "")
        {

            string user_name = "";
            if (HttpContext.Current.Session["user_name"] != null)
                user_name = ",user_name='" + HttpContext.Current.Session["user_name"].ToString() + "'";
            string sql = "";
            if (type == "bh")
                sql = "update dslam_port set account_int='',goidv_int='',account_mytv='',goidv_mytv='',ma_kh='',ten_kh='',trang_thai_mytv='',trang_thai_int='',ip_tinh='',hinh_thuc_int='',diachi_kh='',ngay_dk_int='',bras_ip=''" + user_name + ",status=2 where id=" + DslamPort.id;
            if (type == "dt")
                sql = "update dslam_port set account_int='',goidv_int='',account_mytv='',goidv_mytv='',ma_kh='',ten_kh='',trang_thai_mytv='',trang_thai_int='',ip_tinh='',hinh_thuc_int='',diachi_kh='',ngay_dk_int='',bras_ip=''" + user_name + ",status=0 where id=" + DslamPort.id;
            if (type == "cn")
                sql = "update dslam_port set account_int='" + DslamPort.account_int + "',goidv_int='" + DslamPort.goidv_int + "',account_mytv='" + DslamPort.account_mytv + "',goidv_mytv='" + DslamPort.goidv_mytv + "',ma_kh='" + DslamPort.ma_kh + "',ten_kh='" + DslamPort.ten_kh + "',trang_thai_mytv='" + DslamPort.trang_thai_mytv + "',trang_thai_int='" + DslamPort.trang_thai_int + "',ip_tinh='" + DslamPort.ip_tinh + "',hinh_thuc_int='" + DslamPort.hinh_thuc_int + "',diachi_kh='" + DslamPort.diachi_kh + "',status=" + DslamPort.status + user_name + " where id=" + DslamPort.id;
            if (type == "dc")
            {
                string sql1 = "update dslam_port set account_int='',goidv_int='',account_mytv='',goidv_mytv='',ma_kh='',ten_kh='',trang_thai_mytv='',trang_thai_int='',ip_tinh='',hinh_thuc_int='',diachi_kh='',ngay_dk_int='',bras_ip=''" + user_name + ",status=0 where id=" + DslamPort.id;
                BaseClass.Lib_ExecuteNonQuery(sql1);
                sql = "update dslam_port set account_int='" + DslamPort.account_int + "',goidv_int='" + DslamPort.goidv_int + "'";
                sql += ",account_mytv='" + DslamPort.account_mytv + "',goidv_mytv='" + DslamPort.goidv_mytv + "',ma_kh='" + DslamPort.ma_kh + "'";
                sql += ",ten_kh='" + DslamPort.ten_kh + "',trang_thai_mytv='" + DslamPort.trang_thai_mytv + "',trang_thai_int='" + DslamPort.trang_thai_int + "',ip_tinh='" + DslamPort.ip_tinh + "'";
                sql += ",bras_ip='" + DslamPort.bras_ip + "',ngay_dk_int=to_date('" + DslamPort.ngay_dk_int + "','dd/MM/yyyy HH24:MI:SS')";
                sql += ",hinh_thuc_int='" + DslamPort.hinh_thuc_int + "',diachi_kh='" + DslamPort.diachi_kh + "'" + user_name + "";
                sql += ",system_id='" + DslamPort.system_id + "',status=" + DslamPort.status + " where system_id='" + DslamPort.system_id + "' and cong='" + DslamPort.cong + "'";

            }
            BaseClass.Lib_ExecuteNonQuery(sql);
        }
        public void updateRequestAction(DslamPortModel DslamPort,string id, string type = "")
        {
            string user_name = "";
            string user_name2 = "";
            if (HttpContext.Current.Session["user_name"] != null)
            {
                user_name = ",user_name='" + HttpContext.Current.Session["user_name"].ToString() + "'";
                user_name2 = HttpContext.Current.Session["user_name"].ToString();
            }
            string sql = "";
            string sql2 = "";
            string slthang = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString("d2");
            if (type == "bh")
            {
                sql = "update dslam_port set account_int='',goidv_int='',account_mytv='',goidv_mytv='',ma_kh='',ten_kh='',trang_thai_mytv='',trang_thai_int='',ip_tinh='',hinh_thuc_int='',diachi_kh='',ngay_dk_int='',bras_ip=''" + user_name + ",status=2 where id=" + id;
                
                //sql2 = "insert into DSLAM_PORT_BD_THANG(id,system_id,rackid,selfid,slot,port,vpi,vci,cong,user_name,action_status,ngay_cn,slthang) ";
                //sql2 += " select nvl((select max(id)+1 from dslam_port_bd_thang),1),'" + DslamPort.system_id + "','" + DslamPort.rackid + "','" + DslamPort.selfid + "','" + DslamPort.slot + "','" + DslamPort.port + "','" + DslamPort.vpi + "','" + DslamPort.vci + "'," + DslamPort.cong + ",'" + user_name2 + "',2,to_date('" + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss") + "', 'dd/MM/yyyy hh24:mi:ss')  ,'" + slthang + "'  from dual";
                //BaseClass.Lib_ExecuteNonQuery(sql2);
            }
            if (type == "dt")
            {
                sql = "update dslam_port set account_int='',goidv_int='',account_mytv='',goidv_mytv='',ma_kh='',ten_kh='',trang_thai_mytv='',trang_thai_int='',ip_tinh='',hinh_thuc_int='',diachi_kh='',ngay_dk_int='',bras_ip=''" + user_name + ",status=0 where id=" + id;

                //sql2 = "insert into DSLAM_PORT_BD_THANG(id,system_id,rackid,selfid,slot,port,vpi,vci,cong,user_name,action_status,ngay_cn,slthang) ";
                //sql2 += " select nvl((select max(id)+1 from dslam_port_bd_thang),1),'" + DslamPort.system_id + "','" + DslamPort.rackid + "','" + DslamPort.selfid + "','" + DslamPort.slot + "','" + DslamPort.port + "','" + DslamPort.vpi + "','" + DslamPort.vci + "'," + DslamPort.cong + ",'" + user_name2 + "',4,to_date('" + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss") + "', 'dd/MM/yyyy hh24:mi:ss')  ,'" + slthang + "'  from dual";
                //BaseClass.Lib_ExecuteNonQuery(sql2);
            }
            if (type == "kp")
            {
                sql = "update dslam_port set status=0" + user_name + " where id=" + id;

                //sql2 = "insert into DSLAM_PORT_BD_THANG(id,system_id,rackid,selfid,slot,port,vpi,vci,cong,user_name,action_status,ngay_cn,slthang) ";
                //sql2 += " select nvl((select max(id)+1 from dslam_port_bd_thang),1),'" + DslamPort.system_id + "','" + DslamPort.rackid + "','" + DslamPort.selfid + "','" + DslamPort.slot + "','" + DslamPort.port + "','" + DslamPort.vpi + "','" + DslamPort.vci + "'," + DslamPort.cong + ",'" + user_name2 + "',3,to_date('" + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss") + "', 'dd/MM/yyyy hh24:mi:ss')  ,'" + slthang + "'  from dual";
                //BaseClass.Lib_ExecuteNonQuery(sql2);
            }
            BaseClass.Lib_ExecuteNonQuery(sql);
        }
        public int checkPort(DslamPortModel DslamPort, string type = "")
        {
            string sql = "";
            if (type == "")
                sql = "SELECT * FROM dslam_port WHERE cong='" + DslamPort.cong + "' and status=1 and system_id='" + DslamPort.system_id + "' and id!=" + DslamPort.id;
            else
                sql = "SELECT * FROM dslam_port WHERE cong='" + DslamPort.cong + "'  and status='0' and system_id='" + DslamPort.system_id + "'";
            return BaseClass.Lib_CheckExist(sql);
        }
        public int checkPort2(string id)
        {
            string sql = "";
            sql = "SELECT * FROM dslam_port WHERE status=1 and  id=" + id;
            return BaseClass.Lib_CheckExist(sql);
        }

        public List<SelectListItem> getCongtrongJson(string system_id, string system_id_old, string cong, string Order)
        {
            List<SelectListItem> list = new List<SelectListItem>();
            string sql = "";
            if (system_id == system_id_old)
                sql = "select cong congtrong from dslam_port where status=0 and cong!=" + cong + "  and system_id='" + system_id + "' order by " + Order;
            else
                sql = "select cong congtrong from dslam_port where status=0 and  system_id='" + system_id + "' order by " + Order;
            var odr = BaseClass.Lib_GetDataReader(sql);
            foreach (DataRow item in odr.Rows)
            {
                list.Add(new SelectListItem { Text = item[0].ToString(), Value = item[0].ToString() });

            }

            return list;
        }
        public static List<SelectListItem> getCongJson(string system_id, string Order)
        {
            List<SelectListItem> list = new List<SelectListItem>();
            string sql = "select cong from dslam_port where system_id='" + system_id + "' order by " + Order;
            var odr = BaseClass.Lib_GetDataReader(sql);
            foreach (DataRow item in odr.Rows)
            {
                list.Add(new SelectListItem { Text = item[0].ToString(), Value = item[0].ToString() });

            }

            return list;
        }
        public List<SelectListItem> getKhuvucJson(string quan_huyen_id, string Order)
        {
            List<SelectListItem> list = new List<SelectListItem>();
            int num;
            if (int.TryParse(quan_huyen_id, out num))
            {
                string sql = "select * from xa_phuong where quan_huyen_id=" + Convert.ToInt32(quan_huyen_id) + " order by " + Order;
                var odr = BaseClass.Lib_GetDataReader(sql);
                foreach (DataRow item in odr.Rows)
                {
                    list.Add(new SelectListItem { Text = item[1].ToString(), Value = item[0].ToString() });

                }
            }
            return list;
        }
        public List<SelectListItem> getTodanphoJson(string xa_phuong_id, string Order)
        {
            List<SelectListItem> list = new List<SelectListItem>();
            int num;
            if (int.TryParse(xa_phuong_id, out num))
            {
                string sql = "select * from todanpho where xa_phuong_id=" + Convert.ToInt32(xa_phuong_id) + " and trim(ten) is not null order by regexp_substr(" + Order + ", '^\\D*') nulls first,to_number(regexp_substr(" + Order + ", '\\d+')) ";
                var odr = BaseClass.Lib_GetDataReader(sql);
                foreach (DataRow item in odr.Rows)
                {
                    list.Add(new SelectListItem { Text = item[1].ToString(), Value = item[0].ToString() });

                }
            }
            return list;
        }
        public List<SelectListItem> getDslamJson(int loai,string quan_huyen_id, string Order = "ten,name")
        {
            List<SelectListItem> list = new List<SelectListItem>();
            int num;
            if (int.TryParse(quan_huyen_id, out num))
            {
                string sql = "select ten,name,system_id from dslam a,quan_huyen b where a.loai="+loai+" and a.quan_huyen_id=b.quan_huyen_id  and a.quan_huyen_id=" + Convert.ToInt32(quan_huyen_id) + " order by " + Order;
                var odr = BaseClass.Lib_GetDataReader(sql);
                foreach (DataRow item in odr.Rows)
                {
                    list.Add(new SelectListItem { Text = item[1].ToString(), Value = item[2].ToString() });

                }

            }
            return list;
        }
        public List<SelectListItem> getDslamByKhuvucJson(int loai,string xa_phuong_id, string Order = "name")
        {
            List<SelectListItem> list = new List<SelectListItem>();
            int num;
            if (int.TryParse(xa_phuong_id, out num))
            {
                string sql = "select * from dslam a where a.loai=" + loai + " and a.xa_phuong_id=" + Convert.ToInt32(xa_phuong_id) + " order by " + Order;
                var odr = BaseClass.Lib_GetDataReader(sql);
                foreach (DataRow item in odr.Rows)
                {
                    list.Add(new SelectListItem { Text = item[2].ToString(), Value = item[1].ToString() });

                }
            }
            return list;
        }
        public List<SelectListItem> getDslamByTodanphoJson(int loai ,string todanpho_id, string Order = "name")
        {
            List<SelectListItem> list = new List<SelectListItem>();
            int num;
            if (int.TryParse(todanpho_id, out num))
            {
                string sql = "select * from dslam where dslam.loai=" + loai + "and todanpho_id=" + Convert.ToInt32(todanpho_id) + " order by " + Order;
                var odr = BaseClass.Lib_GetDataReader(sql);
                foreach (DataRow item in odr.Rows)
                {
                    list.Add(new SelectListItem { Text = item[2].ToString(), Value = item[1].ToString() });

                }
            }
            return list;
        }
        //public int checkExist(DslamPortModel dslam)
        //{
        //    string sql = "SELECT * FROM dslam WHERE system_id='" + dslam.system_id + "'";
        //    return BaseClass.Lib_CheckExist(sql);
        //}

       
        internal static int delete(string p)
        {
            string sql = "";
            sql = "DELETE FROM dslam_port WHERE dslam_port.system_id='" + p + "'";
            return BaseClass.Lib_CheckExist(sql);
        }
        //-------------------------------------------------------------------------------
        public void updateDung_luong(string id, string dung_luong)
        {
            string sql = "update dslam_port set dung_luong='" + dung_luong + "' where id=" + id;
            BaseClass.Lib_ExecuteNonQuery(sql);
        }
    
    }


}
